"""
Case Type   : GSC功能模块
Case Name   : 普通行存表表结构变更，gsc缓存功能验证
Create At   : 2022/03/27
Owner       : opentestcase024
Description :
    step1:打开gsc开关;
    step2:重启数据库生效;
    step3:创建数据库;
    step4:创建表插入数据，并查询;
    step5:查看表syscache占用情况;
    step6:增加索引;
    step6.1:查看表及索引syscache占用情况;
    step6.2:使用索引查询;
    step6.3:查看表及索引syscache占用情况;
    step7:增加字段;
    step7.1:查看表及索引syscache占用情况;
    step7.2:使用索引查询;
    step7.3:查看表及索引syscache占用情况;
    step8:修改字段名称;
    step8.1:查看表及索引syscache占用情况;
    step8.2:使用索引查询;
    step9:删除字段;
    step9.1:查看表及索引syscache占用情况;
    step9.2:使用索引查询;
    step9.3:查看表及索引syscache占用情况;
    step10:删除索引;
    step10.1:查看syscache占用情况;
    step10.2:查询表;
    step10.3:查看表syscache占用情况;
    step11:删除表;
    step11.1:查看syscache占用情况;
Expect      :
    step1:打开gsc开关; expect:修改成功
    step2:重启数据库生效; expect:重启成功
    step3:创建数据库; expect:创建成功
    step4:创建表插入数据，并查询; expect:操作成功
    step5:查看表syscache占用情况; expect:缓存信息正确
    step6:增加索引; expect:操作成功
    step6.1:查看表及索引syscache占用情况; expect:表的缓存信息清理
    step6.2:使用索引查询; expect:操作成功
    step6.3:查看表及索引syscache占用情况; expect:表及索引缓存信息正确
    step7:增加字段; expect:操作成功
    step7.1:查看表及索引syscache占用情况; expect:表的缓存信息清理
    step7.2:使用索引查询; expect:操作成功
    step7.3:查看表及索引syscache占用情况; expect:缓存信息正确
    step8:修改字段名称; expect:操作成功
    step8.1:查看表及索引syscache占用情况; expect:表的缓存信息清理
    step8.2:使用索引查询; expect:操作成功
    step9:删除字段; expect:操作成功
    step9.1:查看表及索引syscache占用情况; expect:表的缓存信息清理
    step9.2:使用索引查询; expect:操作成功
    step9.3:查看表及索引syscache占用情况; expect:缓存信息正确
    step10:删除索引; expect:操作成功
    step10.1:查看syscache占用情况; expect:表的缓存信息清理
    step10.2:查询表; expect:操作成功
    step10.3:查看表syscache占用情况; expect:缓存信息正确
    step11:删除表; expect:操作成功
    step11.1:查看syscache占用情况; expect:表的缓存信息清理
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
"""
import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node

pri_sh = CommonSH('PrimaryDbUser')


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_root = Node(node='PrimaryRoot')
        self.db_name = 'db_gsc0025'
        self.tb_name = "tb_gsc0025"
        self.com = Common()
        self.constant = Constant()

    def test_main(self):
        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = '----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表插入数据，并查询; expect:操作成功----'
        self.log.info(step_txt)
        self.create_sql = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name} " \
            f"(id int not null, aa varchar(10) ,id2 int);" \
            f"insert into {self.tb_name} values(generate_series(1,20000), " \
            f"generate_series(1,20000)||'1a', 11);" \
            f"select count(*) from {self.tb_name};"
        result = pri_sh.execut_db_sql(self.create_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败" + step_txt)
        self.assertIn('INSERT 0 20000', result, "执行失败" + step_txt)
        self.assertIn('1 row', result, "执行失败" + step_txt)

        step_txt = '----step5:查看表syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_sql = f"select * from gs_gsc_table_detail() where " \
            f"relname like '{self.tb_name}%';" \
            f"select * from gs_gsc_dbstat_info() " \
            f"where database_name = '{self.db_name}';"
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'id','aa','id2'", check_result, '执行失败:' + step_txt)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step6:增加索引; expect:操作成功----'
        self.log.info(step_txt)
        create_sql = f"create index {self.tb_name}_index on " \
            f"{self.tb_name}(id);"
        result = pri_sh.execut_db_sql(create_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, result,
                      "执行失败" + step_txt)
        step_txt = '----step6.1:查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("0 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step6.2:使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        self.select_sql = f"select count(*) from {self.tb_name} where id>999;"
        result = pri_sh.execut_db_sql(self.select_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step6.3:查看表及索引syscache占用情况; expect:表及索引缓存信息正确----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'id','aa','id2'", check_result, '执行失败:' + step_txt)
        self.assertIn("2 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step7:增加字段; expect:操作成功----'
        self.log.info(step_txt)
        alter_sql = f"alter table {self.tb_name} add " \
            f"(address varchar(100),sex char);"
        result = pri_sh.execut_db_sql(alter_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step7.1:查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step7.2:使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(self.select_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step7.3:查看表及索引syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'id','aa','id2','address','sex'", check_result,
                      '执行失败:' + step_txt)
        self.assertIn("2 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step8:修改字段名称; expect:操作成功----'
        self.log.info(step_txt)
        alter_sql = f"alter table {self.tb_name} rename id to new_id;"
        result = pri_sh.execut_db_sql(alter_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step8.1:查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step8.2:使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        self.select_sql = f"select count(*) from {self.tb_name} " \
            f"where new_id>999;"
        result = pri_sh.execut_db_sql(self.select_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----ste8.3:查看表及索引syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'new_id','aa','id2','address','sex'", check_result,
                      '执行失败:' + step_txt)
        self.assertIn("2 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step9:删除字段; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"alter table {self.tb_name} drop column address;"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step9.1:查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step9.2:使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(self.select_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step9.3:查看表及索引syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'new_id','aa','id2','........pg.dropped.4........',"
                      "'sex'", check_result, '执行失败:' + step_txt)
        self.assertIn("2 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step10:删除索引; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"drop index {self.tb_name}_index;"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('DROP INDEX', result, "执行失败" + step_txt)
        step_txt = '----step10.1:查看syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("0 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step10.2:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(self.select_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step10.3:查看表syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'new_id','aa','id2','........pg.dropped.4........',"
                      "'sex'", check_result, '执行失败:' + step_txt)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step11:删除表; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"drop table if exists {self.tb_name};"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      "执行失败" + step_txt)
        step_txt = '----step11.1:查看syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("0 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = pri_sh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f"enable_global_syscache ={self.init_para1}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            status = pri_sh.exec_cm_ctl(mode='query', param='-Cv')
            self.log.info(status)
            p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
            if 'Primary' in p_status and "cluster_state   : Normal" in status:
                self.log.info('---集群状态正常，无需恢复为初始状态---')
            else:
                recovery = pri_sh.exec_cm_ctl(mode='switchover',
                                              param='-a')
                self.log.info(recovery)
                time.sleep(10)
            self.status_result = pri_sh.get_db_cluster_status('status')

        step_txt = '----teardown断言----'
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
